﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Web.Services;
using System.Data;
using Npgsql;
using NpgsqlTypes;
using System.Web;
using System.Linq; 
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Globalization;
using Sharp; 
using Sharp.Schema; 


namespace Sharp.ObjectBuilder
{ 
    //todo update .proj file to include new objects / remove deelted ones.

    
    public partial class ObjectBuilder : Handler
    {
        Template localTemplate { get; set; }


		[HttpHandler("Data")]
        public void RunObjectBuilder() 
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            context.Response.ContentType = "text/plain";
            context.Response.Buffer = false;
            context.Response.Write("Building the POCO ORM files, a new folder called object will be added to your project, add the the files in there. They are partial classes that you can augment in a different folder so that your custom stuff doesn't get overwritten next time you run this.");

            String connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

           // using (NpgsqlConnection conn = new NpgsqlConnection(connstr))
            {
               // conn.Open();
                 

                string folder =  Path.GetFullPath( PreApplicationStartCode.AssemblyDirectory + "../../" );//context.Server.MapPath("/") + "../" + ConfigurationManager.AppSettings["ProjectName"] + "/");

                if (!Directory.Exists(folder + "objects/dynamic/backup/"))
                {
                    Directory.CreateDirectory(folder + "objects");
                    Directory.CreateDirectory(folder + "objects/dynamic");
                    Directory.CreateDirectory(folder + "objects/dynamic/backup");
                }


                foreach (String oldfile in Directory.GetFiles(folder + "objects/dynamic/backup/"))
                {
                    File.Delete(oldfile);
                }

                foreach (String oldfile in Directory.GetFiles(folder + "objects/dynamic/"))
                {
                    File.Move(oldfile, oldfile.Replace("objects/dynamic/", "objects/dynamic/backup/").Replace(".cs", ".cs.bak"));
                }


				SchemaBuilder sb = new SchemaBuilder();
				Parse parse = sb.parse;

                foreach (Table tbl in parse.Tables)
                {
                    
                    String Filename = folder + "objects/dynamic/" + tbl.Name + "-obj.cs";
                    String Backup = folder + "objects/dynamic/backup/" + tbl.Name + "-obj.bak";
                    
                    //TextWriter tw = new StreamWriter(context.Server.MapPath("/objects/" + Query["name"]+ ".cs"));
                     localTemplate = new Template(Resources.Read["Sharp.SQL.templates.ObjectTemplate.cs"]);
                     localTemplate.Set("namespace",ConfigurationManager.AppSettings["ProjectName"].Else("DAL"));

                    BuildObj(parse, tbl, context);

                    File.WriteAllText(Filename, localTemplate.ToString());
                }
            }

        }

        public String ConvertToCLR(NpgsqlDbType sqltype)
		{ 
            switch (sqltype)
            {
				case NpgsqlDbType.Bigint		: return "Int64";
				case NpgsqlDbType.Boolean    	: return "Boolean";
				case NpgsqlDbType.Bytea      	: return "Byte[]";
				case NpgsqlDbType.Date       	: return "DateTime";
				case NpgsqlDbType.Double     	: return "Double";
				case NpgsqlDbType.Integer    	: return "Int32";
				case NpgsqlDbType.Money      	: return "Decimal";
				case NpgsqlDbType.Numeric    	: return "Decimal";
				case NpgsqlDbType.Real       	: return "Single";
				case NpgsqlDbType.Smallint   	: return "Int16";
				case NpgsqlDbType.Text       	: return "String";
				case NpgsqlDbType.Time       	: return "DateTime"; 
				case NpgsqlDbType.Timestamp  	: return "DateTime";
				case NpgsqlDbType.TimestampTZ	: return "DateTime"; 
				case NpgsqlDbType.Varchar    	: return "String";
				case NpgsqlDbType.Inet       	: return "IPAddress";
				case NpgsqlDbType.Bit        	: return "Boolean"; 
                default: return "object";
            } 
        } 

		public String ConvertToCLR(System.Data.DbType sqltype)
        { 
            switch (sqltype)
            {
				case System.Data.DbType.Int64    : return "Int64";
				case System.Data.DbType.Boolean  : return "Boolean";
				case System.Data.DbType.Binary   : return "Byte[]";
				case System.Data.DbType.Date     : return "DateTime";
				case System.Data.DbType.Double   : return "Double";
				case System.Data.DbType.Int32    : return "Int32"; 
				case System.Data.DbType.Decimal  : return "Decimal";
				case System.Data.DbType.Single   : return "Single";
				case System.Data.DbType.Int16    : return "Int16";
				case System.Data.DbType.String   : return "String"; 
				case System.Data.DbType.Time     : return "DateTime"; 
				case System.Data.DbType.DateTime : return "DateTime"; 
                default: return "object";
            } 
        }

		public String ConvertToCLR (string name)
		{
			return ConvertToCLR(ConvertStringToCLR(name));
		}

		public System.Data.DbType ConvertStringToCLR (string name)
		{
			switch (name.ToLower())
			{
			case "int8" : return System.Data.DbType.Int64;    //int8       
			case "bigint" : return System.Data.DbType.Int64;    //int8       
			case "bigserial" : return System.Data.DbType.Int64;    //int8       
			case "bool" : return System.Data.DbType.Boolean;  //bool       
			case "bytea" : return System.Data.DbType.Binary;   //bytea      
			case "date" : return System.Data.DbType.Date;     //date       
			case "float8" : return System.Data.DbType.Double;   //float8     
			case "int4" : return System.Data.DbType.Int32;    //int4       
			case "money" : return System.Data.DbType.Decimal;  //money      
			case "numeric" : return System.Data.DbType.Decimal;  //numeric    
			case "float4" : return System.Data.DbType.Single;   //float4     
			case "int2" : return System.Data.DbType.Int16;    //int2       
			case "text" : return System.Data.DbType.String;   //text       
			case "time" : return System.Data.DbType.Time;     //time       
			case "timetz" : return System.Data.DbType.Time;     //timetz     
			case "timestamp" : return System.Data.DbType.DateTime; //timestamp  
			case "timestamptz" : return System.Data.DbType.DateTime; //timestamptz 
			case "varchar" : return System.Data.DbType.String;   //varchar    
			case "inet" : return System.Data.DbType.Object;   //inet       
			case "bit" : return System.Data.DbType.Boolean;  //bit         
			case "void" : return System.Data.DbType.Object;  //bit         
			default: throw new Exception(name + " is not a match for a dbtype");
            }  
		}

        public void BuildObj(Parse db, Table table, HttpContext context)
        { 
            string tablename = table.Name;

            foreach (FK idx in table.fks)
            {
                if (!idx.fkcolumn.Name.Like("id"))
                {
                    Template child = new Template(Resources.Read["Sharp.SQL.templates.ObjectTemplate-fk.cs"]);
                    child.Set("ltablename", tablename);
                    child.Set("tablename", tablename.Capitalize());
                    child.Set("fkey", idx.fkcolumn.Name);
                    child.Set("fkeytype", ConvertToCLR(idx.fkcolumn.DataTypeSize));
                    child.Set("list", localTemplate["list"]);
                    localTemplate.Append("FKs", child.ToString());
                }
            }

            foreach (Column col in db.Tables.Single(x=> x.Name.Like(tablename)).Columns)
            {
				bool nullable = !ConvertToCLR(col.DataTypeSize).LikeOne(new string[] {"String", "Bytea"}) && !col.Name.Like("id"); // && !col.Name.Contains("_id");
                localTemplate.Append("properties", string.Format("\n\t\tpublic {0}{2} {1} {{ get; set; }}", ConvertToCLR(col.DataTypeSize), col.Name, nullable.ToCustom("?", "")));
                if (nullable)
                {
                    localTemplate.Append("list", string.Format("\n\t\t\t\t\tif (rs1.{0}.HasValue) _{1}.{0} = rs1.{0}.Value;", col.Name, tablename.Capitalize()));
                    localTemplate.Append("select", string.Format("\n\t\t\t\t\tif (rs1.{0}.HasValue) this.{0} = rs1.{0}.Value;;", col.Name));
                }
                else
                {
                    localTemplate.Append("list", string.Format("\n\t\t\t\t\t_{1}.{0} = rs1.{0};", col.Name, tablename.Capitalize()));
                    localTemplate.Append("select", string.Format("\n\t\t\t\t\tthis.{0} = rs1.{0};", col.Name));
                }
            }



            foreach (Column col in db.Tables.Single(x=> x.Name.Like(tablename)).Columns)
            {
                localTemplate.Append("upsert", string.Format("\n\t\t\t\t\tdal.{0} = this.{0};", col.Name));
            }

           

            //getlists
            foreach (Table tbl in db.Tables)
            { 
                foreach (FK fk in tbl.fks)
                {
                    if (fk.fkcolumn.ParentTable.Name.Like(tablename))
                    {
                        //lists
                        Template child = new Template(Resources.Read["Sharp.SQL.templates.ObjectTemplate-fk.cs"]);
                        child.Set("ltablename", tablename);
                        child.Set("tablename", tablename.Capitalize());
                        child.Set("reftable", fk.RefTable.Capitalize());
                        child.Set("fkey", fk.fkcolumn.Name);
                        child.Set("fkeytype", ConvertToCLR(fk.fkcolumn.DataTypeSize));
                        child.Set("list", localTemplate["list"]);
                        localTemplate.Append("FKs", child.ToString());

                        //parents
                        Template parent = new Template(Resources.Read["Sharp.SQL.templates.ObjectTemplate-parent.cs"]);
                        parent.Set("ltablename", "parent_" + fk.RefTable.ToLower());
                        parent.Set("tablename", fk.RefTable.Capitalize());
                        parent.Set("fkey", fk.fkcolumn.Name);
                        localTemplate.Append("Parents", parent.ToString());
                    }

                    if (fk.RefTable.Like(tablename))
                    {
                        //children
                        Template child = new Template(Resources.Read["Sharp.SQL.templates.ObjectTemplate-child.cs"]);
                        List<String> columns = new List<string>();
                        //foreach (ForeignKeyColumn col in fk.Columns)
                            columns.Add(fk.fkcolumn.Name);
                        child.Set("ltablename", fk.fkcolumn.ParentTable.Name.ToLower() + "_" + String.Join("_", columns.ToArray()));
                        child.Set("tablename", fk.fkcolumn.ParentTable.Name.ToLower().Capitalize());
                        child.Set("reftable", fk.RefTable.Capitalize());
                        child.Set("refkey", "id");
                        child.Set("fkey", String.Join("_", columns.ToArray()));
                        localTemplate.Append("Children", child.ToString());
                    }

                }
            }


            localTemplate.Set("tablename", tablename.Capitalize());
            localTemplate.Set("ltablename", tablename);
        }

        #region IHttpHandler Members

        public bool IsReusable
        {
            get { return false; }
        }

        public void ProcessRequest(HttpContext context)
        {
            
        }

        #endregion
    }
}

	
 		
 		